/*
  FILENAME	: table_qry_func.sql
  Copyright (C) 2013  Testmonger
  See https://maketestdata.codeplex.com/license

  NOTES		: function to return select with rtrim for each column in table

select dbo.ufnTableQryRTRIM(1)
*/

IF OBJECT_ID (N'ufnTableQryRTRIM', N'FN') IS NOT NULL
    DROP FUNCTION ufnTableQryRTRIM;
GO
CREATE FUNCTION ufnTableQryRTRIM(@FileID bigint)
RETURNS varchar(1000) 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret varchar(1000), @tabname varchar(100), @counterid bigint, 
	@maxcounterid bigint, @vobjectid bigint


select @tabname = rtrim(FileTable) from FileDefinition where fileID = @FileID

select @vobjectid = c.object_id 
from sys.columns c, sys.tables t
where t.name like @tabname and c.object_id = t.object_id

select @maxcounterid = count(*) 
from sys.columns c, sys.tables t
where t.name like @tabname and c.object_id = t.object_id

select @ret = 'select ', @counterid = 1

DECLARE @vtable TABLE (counterid int identity, colname varchar(200))
	insert @vtable
	select name from sys.columns
	where object_id = @vobjectid

WHILE @counterid < @maxcounterid
BEGIN
	select @ret = rtrim(@ret)+' RTRIM('+rtrim(colname)+'),'
	from @vtable
	where counterid = @counterid

	set @counterid=@counterid+1
END

select @ret = rtrim(@ret)+' RTRIM('+rtrim(colname)+') from '+@tabname+' order by 1,2'
from @vtable where counterid = @maxcounterid 
	
     IF (@ret IS NULL) 
        SET @ret = '';

    RETURN rtrim(@ret);

END
GO

/*
select count(*) from sys.columns c, sys.tables t
where t.name like 'FileTemp' and c.object_id = t.object_id

select rtrim(FileTable) from FileDefinition where fileID = 1

select c.object_id 
from sys.columns c, sys.tables t
where t.name like 'FileTemp' and c.object_id = t.object_id

select 'RTRIM('+rtrim(name)+'),',*
	from sys.columns where object_id = 917578307

select * from filedefinition

select dbo.ufnTableQryRTRIM(1)
*/